package dao;

import java.sql.*;
import java.text.SimpleDateFormat;

public class Dao {
	protected static Connection conn;
	protected static Statement state;
	protected static Statement s;
	//定义的时间格式 
	static SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
	//静态初始化块，当服务器启动时执行一次
	static{
		try {
			//连接数据库
			Class.forName("com.mysql.jdbc.Driver");
//			System.out.println("driver");
//			conn = DriverManager.getConnection("jdbc:mysql://localhost/myqq","root","root");
//			jdbc:mysql://localhost/db?user=user&password=123456&useUnicode=true&characterEncoding=UTF-8
			conn = DriverManager.getConnection("jdbc:mysql://localhost/myqq?user=root&password=root&useUnicode=true&characterEncoding=UTF-8");
//			System.out.println("数据库连接成功");
			state = conn.createStatement();
//			s = conn.prepareStatement("insert into user(name,password,pic) +values(?,?,?)");
			//			Statement s = conn.prepareStatement("insert into user()");
//			ResultSet set = state.executeQuery("select * from test");
//			while(set.next()){
//				System.out.println(set.getString(1)+set.getString(2)+set.getString(3));
//			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch(SQLException e){
			e.printStackTrace();
		}
	}
	
	/**初始化所有的用户状态为0，既所有用户为下线状态
	 * 
	 */
	public static void init(){
		try {
			state.executeUpdate("update user set status=0 where status=1");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 添加用户，即用户注册
	 */
	public static String addUser(String sName,String pd,int sex){
//		String strRe = "a";
		String id="";
		try {
			String str = "insert into user(name,password,sex) values('"+sName+"','"+pd+"','"+sex+"')";
//System.out.println(str);
			state.executeUpdate(str);
			
			ResultSet set = state.executeQuery("select id from user where name='"+sName+"'");
			while(set.next()){
				id= set.getString(1);
//System.out.println(id);
			}
			state.executeUpdate("create table "+"a"+id+" like demo");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return id;
	}
	/**
	 * 添加好友
	 * @return boolean
	 * */
	public static boolean addFriend(int id1,int id2){
		String str1 = "a"+id1;
		String str2 = "a"+id2;
		try {
			ResultSet set = state.executeQuery("select name from user where id='"+id1+"'");
			String strInsert = "insert into "+str2+"(id) values("+id1+")";
//System.out.println(strInsert);
			if(set.next()){
				ResultSet have = state.executeQuery("select * from "+str2+" where id="+id1);
				if(have.next())
					return true;
				state.executeUpdate(strInsert);
				state.executeUpdate("insert into "+str1+"(id) values("+id2+")");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * 删除好友
	 */
	public static boolean deleteFriend(int id1,int id2){
		String str1 = "a"+id1;
		String str2 = "a"+id2;
		String sql1 = "delete from "+str2+" where id="+id1;
		String sql2 = "delete from "+str1+" where id="+id2;
		try {
			state.executeUpdate(sql1);
			state.executeUpdate(sql2);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return true;
	}
	/**
	 * 用来判断是否含有用此账号
	 * @author jiao
	 * @return -1如果不含用户id，0密码错误，1正确，2用户已经上线
	 */
	public static int hasId(int id,String pd){
		try {
			ResultSet set = state.executeQuery("select password,status from user where id='"+id+"'");
			if(!set.next())
				return -1;
			if(!set.getString(1).equals(pd))
				return 0;
			if(set.getString(2).equals("1"))
				return 2;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 1;
	}
	/**用来判断是否含有此名字
	 * @return -1 不含此用户名 ，1含有此用户名 
	 */
	public static int hasName(String name){
		try {
			ResultSet set = state.executeQuery("select * from user where name='"+name+"'");
			if(!set.next())
				return -1;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 1;
	}
	
	
	/**改变用户的状态，i为0，1
	 * */
	public static void changeStatus(int id,int i){
		try {
			state.executeUpdate("update user set status='"+i+"' where id='"+id+"'");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**获得客户端的信息,并将信息用逗号隔开
	 */
	public static String getMessage(int id){
		String s="" ;
		try {
			ResultSet set = state.executeQuery("select name,pic,motto,sex,birthday,city from user where id='"+id+"'");
			if(set.next()){
				s= set.getString(1)+","+set.getString(2)+","+set.getString(3)+","+set.getString(4)+
				    ","+set.getString(5)+","+set.getString(6);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}
	
	/**获得好友信息
	 */
	public static ResultSet getFriend(String id){
		ResultSet set = null;
		try {
			String s = "select id,name,pic,status,motto,birthday,city from user where id in (select id from "+"a"+id+")";
//System.out.println(s);
			set = state.executeQuery(s);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set;
	}
	/**获得在线好友的id
	 **/
	public static ResultSet getOnlineFriend(String id){
		ResultSet set = null;
		String sa = "a"+id;
		try {
			String s = "select id from user where id in (select id from "+sa+") and status='1'";
			set = state.executeQuery(s);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set;
	}
	/**只获得新添加的好友的信息
	 */
	public static ResultSet getNewFriendMessage(String id){
		ResultSet set = null;
		try {
			set = state.executeQuery("select id,name,pic,status,motto,birthday,city from user where id="+id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set;
	}
	
	/**
	 * 获得新添加的好友的姓名
	 */
	public static ResultSet getNewFriendMessageName(String name){
		ResultSet set = null;
		try {
			set = state.executeQuery("select id,name,pic,status,motto,birthday,city from user where name='"+name+"'");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set;
	}
	
	/**
	 * 按照条件查找用户
	 */
	public static ResultSet getConditionMsg(int age1,int age2,String sex,String city,String isOnline){
		ResultSet set = null;
		String sql = "select id,name from user where ";
		SimpleDateFormat thisdate = new SimpleDateFormat("yy-MM-dd");
		java.util.Date d = new java.util.Date();
//		GregorianCalendar calendar = new GregorianCalendar();
//		long d1 = d.getTime()-age1*365*
		String d1 = thisdate.format(d);
//		String d2 = thisdate.format(d);
//		System.out.println(d1+" "+calendar.YEAR);
		int year1 = 2009-age1;
		int year2 = 2009-age2;
		String newd1 =  year1+d1.substring(2);
		String newd2 = year2+d1.substring(2);
		sql += "birthday<'"+newd1+"' and birthday>'"+newd2+"'";
		
		if(sex.equals("男"))
			sql += " and sex=1";
		else if(sex.equals("女"))
			sql += " and sex=0";
		if(!city.equals("中国"))
			sql += " and city='"+city+"'";
		if(isOnline.equals("在线"))
			sql += " and status=1";
		System.out.println(sql);
		try {
			set = state.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return set;
	}

	/**
	 * 在没上线时，是否有好友发送过信息
	 */
	public static boolean hasWords(String destID,String sourceID){
		try {
			ResultSet set;
			String sql ;
			if(sourceID.length()==0)
				sql = "select msg from message where destID="+destID;
			else
				sql = "select msg from message where destID="+destID+" and sourceID="+sourceID;
			set = state.executeQuery(sql);
			if(set.next())
				return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	
	/**
	 * 获取发送消息的好友的ID
	 */
	public static String getSourceID(String destID){
		try {
			ResultSet set = state.executeQuery("select sourceID from message where destID="+destID);
			if(set.next())
				return set.getString(1);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "";
	}
	
	/**
	 * 获取不在线时，好友发来的信息
	 */
	public static String getWords(String destID,String sourceID){
		
		String s = null;
		try {
			
			ResultSet set = state.executeQuery("select msg from message where destID="+destID+" and sourceID="+sourceID);
			if(set.next())
				s = set.getString(1);
			state.executeUpdate("delete from message where destID="+destID+" and sourceID="+sourceID);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return s;
	}
	
	/**
	 *	如果好友不在线，将向好友发送的信息添加入数据库。 
	 */
	public static void insertWords(String destID,String sourceID,String strNew,String strOld){
		String title = "";
		if(strOld.length()!=0)
			strOld += "\n";
		try {
			
			title += Dao.getName(sourceID)+"  ";
			title += format.format(new java.util.Date())+":\n";
			state.executeUpdate("insert into message(destID,sourceID,msg) values("+destID+","+sourceID+",'"+strOld+title+strNew+"')");
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 	获得ID号的姓名
	 */
	public static String getName(String ID){
		try {
			ResultSet setName = state.executeQuery("select name from user where id="+ID);
			if(setName.next())
				return setName.getString(1);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return "";
	}
	
	/**
	 * 	更新某用户的信息
	 */
	public static void updateUser(String id,String name,String motto,String pic,String sex,String birthday,String city){
		String sql = "update user set name='"+name+"',motto='"+motto+"',pic="+pic+",sex="+sex
		             +",birthday='"+birthday+"',city='"+city+"' where id="+id;
		System.out.println(sql);
		try {
			state.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
